using System;
using System.Data;
using DotNetNuke.Framework.Providers;
using Microsoft.ApplicationBlocks.Data;

using DesertedRoadStudios.BlogNetNuke.Common;

namespace DesertedRoadStudios.BlogNetNuke.Components
{
    public class SqlDataProvider : DataProvider
    {

        #region vars

        private const string providerType = "data";
        private const string moduleQualifier = "DesertedRoadStudios_BlogNetNuke_";

        private ProviderConfiguration providerConfiguration = ProviderConfiguration.GetProviderConfiguration(providerType);
        private string connectionString;
        private string providerPath;
        private string objectQualifier;
        private string databaseOwner;

        #endregion

        #region cstor

        /// <summary>
        /// cstor used to create the sqlProvider with required parameters from the configuration
        /// section of web.config file
        /// </summary>
        public SqlDataProvider()
        {
            Provider provider = (Provider)providerConfiguration.Providers[providerConfiguration.DefaultProvider];
            connectionString = DotNetNuke.Common.Utilities.Config.GetConnectionString();

            if (connectionString == string.Empty)
                connectionString = provider.Attributes["connectionString"];

            providerPath = provider.Attributes["providerPath"];

            objectQualifier = provider.Attributes["objectQualifier"];
            if (objectQualifier != string.Empty && !objectQualifier.EndsWith("_"))
                objectQualifier += "_";

            databaseOwner = provider.Attributes["databaseOwner"];
            if (databaseOwner != string.Empty && !databaseOwner.EndsWith("."))
                databaseOwner += ".";
        }

        #endregion

        #region properties

        public string ConnectionString
        {
            get { return connectionString; }
        }


        public string ProviderPath
        {
            get { return providerPath; }
        }

        public string ObjectQualifier
        {
            get { return objectQualifier; }
        }


        public string DatabaseOwner
        {
            get { return databaseOwner; }
        }

        #endregion

        #region private methods

        private string GetFullyQualifiedName(string name)
        {
            return DatabaseOwner + ObjectQualifier + moduleQualifier + name;
        }

        private object GetNull(object field)
        {
            return DotNetNuke.Common.Utilities.Null.GetNull(field, DBNull.Value);
        }

        #endregion

        #region Override Methods

        // BNNDefault.aspx
        public override IDataReader GetBlogTabInfo(int portalID, int moduleID, string tabPath)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetBlogTabInfo"), portalID, moduleID, tabPath);
        }
        public override IDataReader GetAllBlogTabInfo(int portalID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllBlogTabInfo"), portalID);
        }

        // Module Page_Init
        public override int UpdateTabID(int portalID, int moduleID, int tabID)
        {
            return (int)SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("UpdateTabID"), portalID, moduleID, tabID);
        }

        // Module Host Settings
        public override IDataReader GetFriendlyURLs()
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetFriendlyURLs"));
        }
        public override void InsertFriendlyURL(string path)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("InsertFriendlyURL"), path);
        }
        public override void DeleteFriendlyURL(int pathID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("DeleteFriendlyURL"), pathID);
        }
        public override string GetSetting(int portalID, int moduleID, string setting)
        {
            return (string)SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("GetSetting"), portalID, moduleID, setting);
        }
        public override IDataReader GetSettings(int portalID, int moduleID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetSettings"), portalID, moduleID);
        }
        public override void SetSetting(int portalID, int moduleID, string setting, string value)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("SetSetting"), portalID, moduleID, setting, value);
        }
        public override void ClearSettings(int portalID, int moduleID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("ClearSettings"), portalID, moduleID);
        }

        // Posts
        public override string GetTabPath(int portalID, int tabID, int moduleID)
        {
            return (string)SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("GetTabPath"), portalID, tabID, moduleID);
        }
        public override IDataReader GetAllPosts(int portalID, int moduleID, int tabID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPosts"), portalID, moduleID, tabID);
        }
        public override IDataReader GetAllPublishedPosts(int portalID, int moduleID, int tabID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPublishedPosts"), portalID, moduleID, tabID);
        }
        public override IDataReader GetAllPostsUnderReview(int portalID, int moduleID, int tabID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPostsUnderReview"), portalID, moduleID, tabID);
        }
        public override IDataReader GetAllPostDrafts(int portalID, int moduleID, int tabID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPostDrafts"), portalID, moduleID, tabID);
        }
        public override IDataReader GetAllDeletedPosts(int portalID, int moduleID, int tabID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllDeletedPosts"), portalID, moduleID, tabID);
        }
        public override IDataReader GetPostByDateSlug(int portalID, int moduleID, DateTime publishedDate, string slug)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetPostByDateSlug"), portalID, moduleID, publishedDate, slug);
        }
        public override IDataReader GetPostByPostID(int portalID, int moduleID, Guid postID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetPostByPostID"), portalID, moduleID, postID);
        }
        public override IDataReader GetPostByPostPath(int portalID, int moduleID, string postPath)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetPostByPostPath"), portalID, moduleID, postPath);
        }
        public override IDataReader GetAllPostsByDate(int portalID, int moduleID, int tabID, DateTime publishedDate)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPostsByDate"), portalID, moduleID, tabID, publishedDate);
        }
        public override IDataReader GetAllPostsByDateRange(int portalID, int moduleID, int tabID, DateTime beginDate, DateTime endDate)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPostsByDateRange"), portalID, moduleID, tabID, beginDate, endDate);
        }
        public override object ValidatePostTitle(int portalID, int moduleID, int tabID, string slug)
        {
            return SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("ValidatePostTitle"), portalID, moduleID, tabID, slug);
        }
        public override void InsertPost(PostInfo post)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("InsertPost"), post.PostID, post.PortalID, post.ModuleID, post.PostTitle, post.Slug, post.Content, post.Excerpt, post.AllowComments, post.Status, post.PublishedDate, post.PostPath, post.Keywords, post.Description, post.AuthorID);
        }
        public override void UpdatePost(PostInfo post)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("UpdatePost"), post.PostID, post.PortalID, post.ModuleID, post.PostTitle, post.Slug, post.Content, post.Excerpt, post.AllowComments, post.Status, post.PublishedDate, post.PostPath, post.Keywords, post.Description, post.AuthorID);
        }
        public override void SetPostStatus(int portalID, int moduleID, Guid postID, PostStatus status)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("SetPostStatus"), portalID, moduleID, postID, status);
        }
        public override IDataReader GetAllPostsForCategory(int portalID, int moduleID, string slug)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPostsForCategory"), portalID, moduleID, slug);
        }
        public override IDataReader GetAllPostsForTag(int portalID, int moduleID, string slug)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllPostsForTag"), portalID, moduleID, slug);
        }

        // Comments
        public override IDataReader GetAllCommentsForPost(int portalID, int moduleID, Guid postID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllCommentsForPost"), portalID, moduleID, postID);
        }
        public override void AddCommentToPost(CommentInfo comment)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("AddCommentToPost"), comment.CommentID, comment.PortalID, comment.ModuleID, comment.PostID, comment.CommentTitle, GetNull(comment.AuthorID), GetNull(comment.AuthorName), comment.Email, comment.IPAddress, GetNull(comment.URL), comment.CommentDate, comment.Comments, comment.IsApproved, comment.IsDeleted);
        }
        public override void ApproveComment(Guid commentID, int portalID, int moduleID, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("ApproveComment"), commentID, portalID, moduleID, postID);
        }
        public override void UnApproveComment(Guid commentID, int portalID, int moduleID, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("UnApproveComment"), commentID, portalID, moduleID, postID);
        }
        public override void DeleteComment(Guid commentID, int portalID, int moduleID, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("DeleteComment"), commentID, portalID, moduleID, postID);
        }
        public override void UnDeleteComment(Guid commentID, int portalID, int moduleID, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("UnDeleteComment"), commentID, portalID, moduleID, postID);
        }

        // Categories
        public override IDataReader GetAllCategories(int portalID, int moduleID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllCategories"), portalID, moduleID);
        }
        public override int AddCategory(CategoryInfo category)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("AddCategory"), category.Category, category.Slug, category.ParentID, category.PortalID, category.ModuleID, GetNull(category.Keywords), GetNull(category.Description)));
        }
        public override void UpdateCategory(CategoryInfo category)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("UpdateCategory"), category.CategoryID, category.Category, category.Slug, category.ParentID, GetNull(category.Keywords), GetNull(category.Description));
        }
        public override void AddCategoryToPost(int categoryID, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("AddCategoryToPost"), categoryID, postID);
        }
        public override IDataReader GetAllCategoriesForPost(Guid postID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllCategoriesForPost"), postID);
        }
        public override IDataReader GetAllCategoriesForPosts(int portalID, int moduleID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllCategoriesForPosts"), portalID, moduleID);
        }
        public override void DeleteAllCategoriesForPost(Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("DeleteAllCategoriesForPost"), postID);
        }

        // Tags
        public override int AddTag(TagInfo tag)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("AddTag"), tag.Tag, tag.Slug, tag.PortalID, tag.ModuleID));
        }
        public override void AddTagToPost(int tagID, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("AddTagToPost"), tagID, postID);
        }
        public override int GetTagID(string tag, int portalID, int moduleID)
        {
            return Convert.ToInt32(SqlHelper.ExecuteScalar(connectionString, GetFullyQualifiedName("GetTagID"), tag, portalID, moduleID));
        }
        public override IDataReader GetAllTags(int portalID, int moduleID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllTags"), portalID, moduleID);
        }
        public override IDataReader GetAllTagsForPost(Guid postID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllTagsForPost"), postID);
        }
        public override IDataReader GetAllTagsForPosts(int portalID, int moduleID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetAllTagsForPosts"), portalID, moduleID);
        }
        public override void DeleteAllTagsForPost(Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("DeleteAllTagsForPost"), postID);
        }

        // TinyURLs
        public override void AddTinyUrl(string domainName, int moduleID, bool logRedirects)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("AddTinyUrl"), domainName, moduleID, logRedirects);
        }

        public override void AddTinyUrlRedirect(int tinyURLID, string code, Guid postID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("AddTinyUrlRedirect"), tinyURLID, code, postID);
        }

        public override void AddTinyUrlLog()
        {
        }

        public override void DeleteTinyUrlLog(int redirectID)
        {
            SqlHelper.ExecuteNonQuery(connectionString, GetFullyQualifiedName("DeleteTinyUrlLog"), redirectID);
        }

        public override IDataReader GetTinyUrlLog(int redirectID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetTinyUrlLog"), redirectID);
        }

        public override IDataReader GetTinyUrl(Guid postID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetTinyUrl"), postID);
        }

        public override IDataReader GetTinyUrlDomain(int moduleID)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetTinyUrlDomain"), moduleID);
        }

        public override IDataReader GetPostForTinyUrl(string domain, string code)
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetPostForTinyUrl"), domain, code);
        }

        public override IDataReader GetTinyUrlDomains()
        {
            return (IDataReader)SqlHelper.ExecuteReader(connectionString, GetFullyQualifiedName("GetTinyUrlDomains"));
        }

        #endregion
    }
}
